with t_taking as (--揽收
    select *
    from (
             select billcode
                  , scansitecode
                  , inputsite
                  , scantime
                  , row_number() over (partition by billcode, scansitecode order by scantime ) rnk
             from jms_ods.tab_barscan_taking
             where dt between date_add('{{ execution_date | date_add(1) | cst_ds }}', -12) and '{{ execution_date | date_add(1) | cst_ds }}'
         ) a
    where a.rnk = 1
),
     t_warehousing as (--入仓
         select *
         from (
                  select billcode
                       , scansitecode
                       , inputsite
                       , scantime
                       , row_number() over (partition by billcode, scansitecode order by scantime ) rnk
                  from jms_ods.tab_barscan_warehousing
                  where dt between date_add('{{ execution_date | date_add(1) | cst_ds }}', -12) and '{{ execution_date | date_add(1) | cst_ds }}'
              ) a
         where a.rnk = 1
     ),
     t_pick_network as (
         select *
         from (
                  select billcode
                       , scansitecode
                       , inputsite
                       , scantime
                       , row_number() over (partition by billcode order by scantime ) rnk
                  from (
                           select *
                           from t_taking
                           union all
                           select *
                           from t_warehousing
                       ) a
              ) b
         where b.rnk = 1
     ),
     t_center_send as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         inputsite,
                         nextstationcode,
                         nextstation,
                         scantime,
                         transfercode,
                         '中心发件' as                                                                     scantype,
                         row_number() over (partition by billcode,scansitecode order by scantime desc) rnk
                  from jms_ods.tab_barscan_centersend
                  where dt between date_add('{{ execution_date | date_add(1) | cst_ds }}', -2) and date_add('{{ execution_date | date_add(1) | cst_ds }}', -1)
                    and scantime between concat(date_add('{{ execution_date | date_add(1) | cst_ds }}', -2), ' 06:00:00') and concat(date_add('{{ execution_date | date_add(1) | cst_ds }}', -1), ' 05:59:59')
                    and (nextstation like '%集散%' or nextstation like '%转运中心%')
                    and expresstype = '公路运输'
              ) a
         where a.rnk = 1
           and a.billcode not like 'B%'
     ),
     t_center_arrival as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         inputsite,
                         scantime,
                         row_number() over (partition by billcode,scansitecode order by scantime ) rnk
                  from jms_ods.tab_barscan_centerarrival
                  where dt between date_add('{{ execution_date | date_add(1) | cst_ds }}', -3) and date_add('{{ execution_date | date_add(1) | cst_ds }}', -1)
              ) a
         where a.rnk = 1
           and a.billcode not like 'B%'
     ),
     t_first_center as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         row_number() over (partition by billcode order by scantime asc) rnk
                  from jms_ods.tab_barscan_centersend
                  where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -7)
              ) a
         where a.rnk = 1
     ),
     t_end_center as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         inputsite,
                         row_number() over (partition by billcode order by scantime desc) rnk
                  from jms_ods.tab_barscan_centersend
                  where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -2)
              ) a
         where a.rnk = 1
     ),
     t_other as (--拆包、封发车，取拆包目的中心、封发车时间
         select *
         from (
                  select billcode,
                         packagecode,
                         scantype,
                         inputsite,
                         scansitecode,
                         scantime,
                         transfercode,
                         row_number() over (partition by transfercode,scantype,scansitecode order by scantime desc) rnk --TODO 封发车时间取最晚还是最早
                  from jms_ods.tab_barscan_other
                  where dt between date_add('{{ execution_date | date_add(1) | cst_ds }}', -5) and date_add('{{ execution_date | date_add(1) | cst_ds }}', 0)
                    and scantype = '封发车扫描'
              ) a
         where a.rnk = 1
     ),
     t_loading as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         inputsite,
                         nextstationcode,
                         nextstation,
                         transfercode,
                         scantime,
                         row_number() over (partition by billcode,scansitecode,transfercode order by scantime desc ) rnk
                  from jms_ods.tab_barscan_loading
                  where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -5)
              ) a
         where a.rnk = 1
     ),
     t_ship_loading as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         inputsite,
                         nextstationcode,
                         nextstation,
                         transfercode,
                         scantime,
                         row_number() over (partition by scansitecode,transfercode order by scantime desc ) rnk
                  from t_loading
              ) a
         where a.rnk = 1
     ),
     t_unloading as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         inputsite,
                         nextstationcode,
                         nextstation,
                         transfercode,
                         scantime,
                         row_number() over (partition by billcode,scansitecode order by scantime asc ) rnk
                  from jms_ods.tab_barscan_unloading
                  where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -5)
              ) a
         where a.rnk = 1
     ),
     t_arrival as (
         select *
         from (
                  select billcode,
                         scansitecode,
                         inputsite,
                         nextstationcode,
                         nextstation,
                         transfercode,
                         scantime,
                         row_number() over (partition by billcode,scansitecode order by scantime asc ) rnk
                  from jms_ods.tab_barscan_arrival
                  where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -5)
              ) a
         where a.rnk = 1
     ),
     t_union_shipment as (
         select shipment_no, shipment_name
         from (
                  select shipment_no, shipment_name
                  from jms_ods.yl_tmsnew_branch_shipment
                  where dt = '{{ execution_date | date_add(1) | cst_ds }}'
                  union all
                  select shipment_no, shipment_name
                  from jms_ods.yl_tmsnew_tms_shipment
                  where dt = '{{ execution_date | date_add(1) | cst_ds }}'
              )
         group by shipment_no, shipment_name
     )
        ,
     t_union_shipment_stop as (--支线干线经停点
         select a.shipment_no,
                t_union_shipment.shipment_name,
                sort_num,
                network_code,
                network_name,
                planned_arrival_time,
                planned_departure_time,
                actual_arrival_time,
                actual_departure_time,
                last_network_code,
                next_network_code,
                case
                    when a.last_network_code is not null
                        and a.next_network_code is not null then 1 --经停点
                    else 2 --非经停点
                    end as stop_type
         from (
                  select *,
                         lag(network_code) over w  as last_network_code, --上一站
                         lead(network_code) over w as next_network_code
                  from (
                           select shipment_no,
                                  sort_num,
                                  network_code,
                                  network_name,
                                  planned_arrival_time,
                                  planned_departure_time,
                                  actual_arrival_time,
                                  actual_departure_time
                           from jms_ods.yl_tmsnew_tms_shipment_stop
                           where dt = '{{ execution_date | date_add(1) | cst_ds }}'
                           union all
                           select shipment_no,
                                  sort_num,
                                  network_code,
                                  network_name,
                                  planned_arrival_time,
                                  planned_departure_time,
                                  actual_arrival_time,
                                  actual_departure_time
                           from jms_ods.yl_tmsnew_branch_shipment_stop
                           where dt = '{{ execution_date | date_add(1) | cst_ds }}'
                       ) a
                  group by shipment_no,
                           sort_num,
                           network_code,
                           network_name,
                           planned_arrival_time,
                           planned_departure_time,
                           actual_arrival_time,
                           actual_departure_time
                      window w as (partition by shipment_no order by sort_num)
              ) a
                  left join t_union_shipment t_union_shipment
                            on a.shipment_no = t_union_shipment.shipment_no
     ),
     t_branch_ship_stop as (
         select shipment_no,
                network_code,
                network_name,
                planned_departure_time,
                planned_arrival_time,
                actual_arrival_time,
                actual_departure_time,
                sort_num,
                lag(network_code) over w  as last_network_code, --上一站
                lead(network_code) over w as next_network_code
         from jms_ods.yl_tmsnew_branch_shipment_stop
         where dt = '{{ execution_date | date_add(1) | cst_ds }}'
         group by shipment_no, network_code, network_name, planned_departure_time, planned_arrival_time, actual_arrival_time, actual_departure_time, sort_num
             window w as (partition by shipment_no order by sort_num asc )
     ),
     t_ship_stop as (
         select shipment_no,
                network_code,
                network_name,
                planned_departure_time,
                planned_arrival_time,
                actual_arrival_time,
                actual_departure_time,
                sort_num,
                lag(network_code) over w  as last_network_code, --上一站
                lead(network_code) over w as next_network_code
         from jms_ods.yl_tmsnew_tms_shipment_stop
         where dt = '{{ execution_date | date_add(1) | cst_ds }}'
         group by shipment_no, network_code, network_name, planned_departure_time, planned_arrival_time, actual_arrival_time, actual_departure_time, sort_num
             window w as (partition by shipment_no order by sort_num)
     ),
     t_branch_ship as (
         select shipment_no, shipment_name
         from jms_ods.yl_tmsnew_branch_shipment
         where dt = '{{ execution_date | date_add(1) | cst_ds }}'
         group by shipment_no, shipment_name
     ),
     t_ship as (
         select shipment_no, shipment_name
         from jms_ods.yl_tmsnew_tms_shipment
         where dt = '{{ execution_date | date_add(1) | cst_ds }}'
         group by shipment_no, shipment_name
     ),
     t_send_record as (--航空发件扫描表
         select waybill_no,
                send_scan_time,  --航空发件时间
                booking_space_no --订舱号
         from jms_ods.tab_aviation_send_record
         where dt = '{{ execution_date | date_add(1) | cst_ds }}'
           and send_scan_time between concat(date_add('{{ execution_date | date_add(1) | cst_ds }}', -2), ' 06:00:00') and concat(date_add('{{ execution_date | date_add(1) | cst_ds }}', -1), ' 05:59:59')
         group by waybill_no,
                  send_scan_time, --航空发件时间
                  booking_space_no --订舱号
     ),
     t_goods_report as ( --发货上报
         select operation_time,     --登记/录入时间（发货上报）
                flight_no,          --航班号
                origin_center_code, --始发转运中心
                origin_center_name,
                take_off_time,      --实际起飞时间
                booking_space_no,   --订舱号
                destination_center_code,
                destination_center_name
         from jms_ods.tab_send_goods_report
         where dt = '{{ execution_date | date_add(1) | cst_ds }}'
         group by operation_time,     --登记/录入时间（发货上报）
                  flight_no,          --航班号
                  origin_center_code, --始发转运中心
                  origin_center_name,
                  take_off_time,      --实际起飞时间
                  booking_space_no,   --订舱号
                  destination_center_code,
                  destination_center_name
     ),
     t_flight_send as (
         select *
         from (
                  select t_send_record.waybill_no               as                                                                                               billcode,
                         t_goods_report.origin_center_code      as                                                                                               scansitecode,
                         t_goods_report.origin_center_name      as                                                                                               inputsite,
                         t_goods_report.destination_center_code as                                                                                               nextstationcode,
                         t_goods_report.destination_center_name as                                                                                               nextstation,
                         t_send_record.send_scan_time           as                                                                                               scantime,
                         ''                                     as                                                                                               transfercode,
                         '航空发件'                                 as                                                                                               scantype,
                         row_number() over (partition by t_send_record.waybill_no,t_goods_report.origin_center_code order by t_send_record.send_scan_time desc ) rnk
                  from t_send_record t_send_record
                           join t_goods_report t_goods_report
                                on t_send_record.booking_space_no = t_goods_report.booking_space_no
              ) a
         where a.rnk = 1
           and a.billcode not like 'B%'
     ),
     t_second as (
         select a.code                   as second_code,
                a.network_id,
                a.network_code,
                a.network_name,
                coalesce(b.id, e.id)     as center_id,
                coalesce(b.code, e.code) as center_code,
                coalesce(b.name, e.name) as center_name
         from jms_dim.dim_lmdm_sys_second_code a
                  left join jms_dim.dim_lmdm_sys_network_distributi b --网点的派件中心
                            on a.network_id = b.network_id
                                and b.distribution_type = 1
                  left join jms_dim.dim_lmdm_sys_network_distributi c --网点的派件集散
                            on a.network_id = c.network_id
                                and c.distribution_type = 4
                  left join jms_dim.dim_lmdm_sys_network_distributi e --网点派件集散的派件中心
                            on c.id = e.network_id
                                and e.distribution_type = 1
         where a.is_enable = 1
           and a.is_delete = 1
     ),
     t_first as (
         select first_code, center_code, max(center_name) as center_name
         from (
                  select a.code                                               as first_code,
                         if(a.center_name like '%集散点', b.code, a.center_code) as center_code,
                         if(a.center_name like '%集散点', b.name, a.center_name) as center_name
                  from jms_dim.dim_lmdm_sys_first_code a
                           left join jms_dim.dim_lmdm_sys_network_distributi b
                                     on a.center_id = b.network_id
                                         and b.distribution_type = 1
                  where a.is_enable = 1
                    and a.is_delete = 1
              ) a
         group by first_code, center_code
     ),
     t_threecode as (
         select first_code,
                second_code,
                max(network_id)   as network_id,
                network_code,
                max(network_name) as network_name,
                center_code,
                max(center_id)    as center_id,
                max(center_name)  as center_name
         from (
                  select t_first.first_code,
                         t_second.second_code,
                         t_second.network_id,
                         t_second.network_code,
                         t_second.network_name,
                         t_second.center_id,
                         t_second.center_code,
                         t_second.center_name
                  from t_second t_second
                           left join t_first t_first
                                     on t_second.center_code = t_first.center_code
              ) a
         group by first_code, second_code, network_code, center_code
     ),
     t_waybill as (
         select *
         from (
                  select waybill_no,
                         pick_network_code,
                         pick_network_name,
                         pick_finance_code,
                         pick_finance_name,
                         sender_province_id,
                         sender_province_name,
                         sender_city_id,
                         sender_city_name,
                         sender_area_id,
                         sender_area_name,
                         sign_network_code,
                         sign_network_name,
                         dispatch_network_code,
                         dispatch_network_name,
                         dispatch_finance_name,
                         dispatch_finance_code,
                         receiver_city_id,
                         receiver_city_name,
                         receiver_area_id,
                         receiver_area_name,
                         terminal_dispatch_code,
                         is_refund,
                         split(regexp_replace(terminal_dispatch_code, ' ', ','), ',')[0] as                   first_code,
                         split(regexp_replace(terminal_dispatch_code, ' ', ','), ',')[1] as                   second_code,
                         row_number() over (partition by waybill_no order by sign_time,last_update_time desc) rnk
                  from jms_ods.yl_oms_oms_waybill
                  where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -5)
              ) a
         where a.rnk = 1
     ),
     t_send as (
         select billcode,
                scansitecode,
                inputsite,
                nextstationcode,
                nextstation,
                scantime,
                transfercode,
                scantype
         from t_flight_send
         union all
         select t_center_send.billcode,
                t_center_send.scansitecode,
                t_center_send.inputsite,
                t_center_send.nextstationcode,
                t_center_send.nextstation,
                t_center_send.scantime,
                t_center_send.transfercode,
                t_center_send.scantype
         from t_center_send t_center_send
                  left anti
                  join t_flight_send t_flight_send
                       on t_center_send.billcode = t_flight_send.billcode
                           and t_center_send.scansitecode = t_flight_send.scansitecode
     ),
     t_interceptorrecord as ( --拦截件
         select *
         from (
                  select waybill_no,
                         intercept_network_code,
                         intercept_network_name,
                         intercept_time,
                         row_number() over (partition by waybill_no,intercept_network_code order by intercept_time desc) rnk,
                         dt
                  from jms_ods.yl_oms_interceptorpiece
                  where dt = '{{ execution_date | date_add(1) | cst_ds }}'
              ) a
         where a.rnk = 1
     ),
     t_difficult as ( --问题件
         select *
         from (
                  select billcode,
                         scansitecode,
                         scantime,
                         row_number() over (partition by billcode,scansitecode order by scantime desc) rnk
                  from jms_ods.tab_barscan_difficult
                  where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -10)
              ) a
         where a.rnk = 1
     ),
     t_reback as (
         select *
         from (
                  select waybill_no
                       , apply_type_code
                       , apply_network_code
                       , apply_network_name
                       , apply_time
                       , row_number() over (partition by waybill_no order by apply_time desc ) rnk
                  from jms_ods.tab_reback_transfer_express
                  where dt = date_add('{{ execution_date | date_add(1) | cst_ds }}', -1)
              ) a
         where a.rnk = 1
     ),
     t_sign as (
         select *
         from (
                  select *, row_number() over (partition by billcode order by scantime) rnk
                  from (select billcode, scansitecode, inputsite, scantime
                        from jms_ods.tab_barscan_collect
                        where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -2)
                          and scantype = '出库扫描'
                        union all
                        select billcode, scansitecode, inputsite, delivertime as scantime
                        from jms_ods.tab_barscan_sign
                        where dt >= date_add('{{ execution_date | date_add(1) | cst_ds }}', -2)) a
              ) b
         where b.rnk = 1
     ),
     t_first_code as (
         select code, center_code, center_name
         from jms_dim.dim_lmdm_sys_first_code
         where IS_DELETE = 1 --启用
           and IS_ENABLE = 1 -- 未删除
         group by code, center_code, center_name
     ),
     t_network as (
         select *
         from jms_dim.dim_sys_network_detail_dt t_network
         where dt = date_add('{{ execution_date | date_add(1) | cst_ds }}', -1)
     ),
     t_send_end as (
         select t_send.billcode                                                                                                                                            as billcode,
                t_loading.transfercode,
                t_unloading.transfercode,
                t_pick_network.scansitecode                                                                                                                                as send_network_code,
                t_send.scansitecode                                                                                                                                        as center_code,
                t_send.inputsite                                                                                                                                           as center_name,
                if(t_first_center.scansitecode is not null, 1, 2)                                                                                                          as is_first_center,--1是2否
                coalesce(t_arrival_branch.shipment_no, t_arrival_branch_loading.shipment_no)                                                                               as arrival_shipment_no,
                case
                    when t_arrival_branch.actual_arrival_time is not null then t_arrival_branch.actual_arrival_time
                    when t_arrival_branch_loading.actual_arrival_time is not null then t_arrival_branch_loading.actual_arrival_time
                    else t_unloading.scantime
                    end                                                                                                                                                    as arrival_shipment_time,
                least(nvl(t_unloading.scantime, timestamp('3999-01-01 00:00:00')), nvl(t_center_arrival.scantime, timestamp('3999-01-01 00:00:00')),
                      nvl(t_arrival.scantime, timestamp('3999-01-01 00:00:00')))                                                                                           as arrival_time,
                t_send.nextstation                                                                                                                                         as next_center_name,
                t_send.nextstationcode                                                                                                                                     as next_center_code,
                t_send.scantime                                                                                                                                            as send_time,
                t_send_branch.actual_departure_time                                                                                                                        as send_shipment_time,
                t_send_branch.shipment_name                                                                                                                                as send_shipment_name,
                t_send.transfercode                                                                                                                                        as send_shipment_no,
                t_send.scantype                                                                                                                                            as scantype,
                case
                    when t_reback.apply_type_code = 4 then '退件拦截'
                    when t_reback.apply_type_code = 5 then '转寄拦截'
                    when t_difficult.scansitecode is not null then '问题件'
                    else '正常件'
                    end                                                                                                                                                    as error_scantype,
                case
                    when t_interceptorrecord_detail.intercept_network_code is not null then t_interceptorrecord_detail.intercept_time
                    when t_difficult.scansitecode is not null then t_difficult.scantime
                    else ''
                    end                                                                                                                                                    as error_scantime,
                coalesce(t_sign.scansitecode, t_waybill.dispatch_network_code, t_threecode1.network_code)                                                                  as sign_network_code,
                if(t_sign.scansitecode is not null, t_end_center.inputsite, t_first_code.center_name)                                                                      as end_center_name,
                if(t_sign.scansitecode is not null, t_end_center.scansitecode, t_first_code.center_code)                                                                   as end_center_code,
                t_last_send_stop.planned_arrival_time                                                                                                                      as send_plan_arrival_time,
                coalesce(t_other.scantime, t_ship_loading.scantime)                                                                                                        as send_pack_send_time,
                t_last_send_stop.actual_arrival_time                                                                                                                       as send_actual_arrival_time,
                row_number() over (partition by t_send.billcode,t_send.scansitecode order by t_unloading.scantime desc,t_loading.scantime desc,t_center_arrival.scantime ) as rnk
         from t_send t_send
                  left join t_pick_network t_pick_network
                            on t_send.billcode = t_pick_network.billcode
                  left join t_waybill t_waybill
                            on t_send.billcode = t_waybill.waybill_no
                  left join t_threecode t_threecode1
                            on t_waybill.first_code = t_threecode1.first_code
                                and t_waybill.second_code = t_threecode1.second_code
                  left join t_unloading t_unloading--卸车
                            on t_send.billcode = t_unloading.billcode
                                and t_send.scansitecode = t_unloading.scansitecode
                  left join t_center_arrival t_center_arrival
                            on t_send.billcode = t_center_arrival.billcode
                                and t_send.scansitecode = t_center_arrival.scansitecode
                  left join t_arrival t_arrival
                            on t_send.billcode = t_arrival.billcode
                                and t_send.scansitecode = t_arrival.scansitecode
                  left join t_loading t_loading--装车，关联下一站
                            on t_send.billcode = t_loading.billcode
                                and t_send.scansitecode = t_loading.nextstationcode
                  left join t_union_shipment_stop t_arrival_branch--卸车支线
                            on t_unloading.transfercode = t_arrival_branch.shipment_no
                                and t_unloading.scansitecode = t_arrival_branch.network_code
                                and t_pick_network.scansitecode = t_arrival_branch.last_network_code
                                and t_arrival_branch.last_network_code is not null
                  left join t_union_shipment_stop t_arrival_branch_loading--装车支线
                            on t_loading.transfercode = t_arrival_branch_loading.shipment_no
                                and t_loading.nextstationcode = t_arrival_branch_loading.network_code
                                and t_arrival_branch_loading.last_network_code is not null
                                and t_arrival_branch_loading.last_network_code = t_loading.scansitecode
                  left join t_first_center t_first_center
                            on t_send.billcode = t_first_center.billcode
                                and t_send.scansitecode = t_first_center.scansitecode
                  left join t_union_shipment_stop t_send_branch
                            on t_send.transfercode = t_send_branch.shipment_no
                                and t_send.scansitecode = t_send_branch.network_code
                  left join t_other t_other
                            on t_send.transfercode = t_other.transfercode
                                and t_send.scansitecode = t_other.scansitecode
                  left join t_ship_loading t_ship_loading
                            on t_send_branch.shipment_no = t_ship_loading.transfercode
                                and t_send_branch.network_code = t_ship_loading.scansitecode
                  left join t_interceptorrecord t_interceptorrecord_detail
                            on t_send.billcode = t_interceptorrecord_detail.waybill_no
                                and t_send.scansitecode = t_interceptorrecord_detail.intercept_network_code
                                and t_send.scantime >= t_interceptorrecord_detail.intercept_time
                  left join t_reback t_reback
                            on t_send.billcode = t_reback.waybill_no
                  left join t_difficult t_difficult
                            on t_send.billcode = t_difficult.billcode
                                and t_send.scansitecode = t_difficult.scansitecode
                                and t_arrival.scantime <= t_difficult.scantime
                                and t_send.scantime >= t_difficult.scantime
                  left join t_end_center t_end_center
                            on t_send.billcode = t_end_center.billcode
                  left join t_sign t_sign
                            on t_send.billcode = t_sign.billcode
                  left join t_first t_first_code
                            on t_waybill.first_code = t_first_code.first_code
                  left join t_union_shipment_stop t_last_send_stop
                            on t_send.transfercode = t_last_send_stop.shipment_no
                                and t_send.scansitecode = t_last_send_stop.network_code
     )
insert overwrite table jms_dm.dm_cn_export_departure_store_dt partition ( dt )
select t_send_end.billcode,
       t_network_pick.agent_name                     as send_agent_name,
       t_network_pick.provider_desc                  as send_province_name,
       t_network_pick.city_desc                      as send_city_name,
       t_network_pick.area_desc                      as send_area_name,
       t_network_pick.name                           as send_network_name,
       t_send_end.center_name,
       t_send_end.is_first_center,--1是2否
       t_send_end.arrival_shipment_no,
       t_send_end.arrival_shipment_time,
       if(t_send_end.arrival_time = timestamp('3999-01-01 00:00:00'), '', t_send_end.arrival_time),
       t_send_end.next_center_name,
       t_send_end.send_time,
       t_send_end.send_shipment_time,
       t_send_end.send_shipment_name,
       t_send_end.send_shipment_no,
       t_send_end.scantype,
       t_send_end.error_scantype,
       t_send_end.error_scantime,
       t_send_end.end_center_name,
       t_network.agent_name                          as sign_agent_name,
       t_network.city_desc                           as sign_city_name,
       t_network.area_desc                           as sign_area_name,
       t_network.name                                as sign_network_name,
       t_network_pick.agent_code                     as send_agent_code,
       t_network_pick.provider_id                    as send_province_code,
       t_network_pick.city_id                        as send_city_code,
       t_network_pick.area_id                        as send_area_code,
       t_network_pick.code                           as send_network_code,
       t_send_end.center_code,
       t_send_end.next_center_code,
       t_send_end.end_center_code,
       t_network.agent_code                          as sign_agent_code,
       t_network.city_id                             as sign_city_code,
       t_network.area_id                             as sign_area_code,
       t_network.code                                as sign_network_code,
       send_plan_arrival_time,
       send_pack_send_time,
       send_actual_arrival_time,
       '{{ execution_date | date_add(1) | cst_ds }}' as dt
from t_send_end t_send_end
         left join t_network t_network
                   on t_send_end.sign_network_code = t_network.code
         left join t_network t_network_pick
                   on t_send_end.send_network_code = t_network_pick.code
where t_send_end.rnk = 1
    DISTRIBUTE BY pmod(hash(billcode), 10);
